

* Combined Effects of Capacity and Time on Fares: Insights from the Yield Management of a Low-Cost Airline
* by Marco Alderighi, Marcella Nicolini and Claudio Piga


* This Stata do-file reproduces all tables and figures reported in the paper.
* It runs on two different files:  
* - tables 1-5 are obtained using "DATA_Alderighi_Nicolini_Piga_descriptive_tables.dta"  
* - tables 6-11 and figures 1-2 are obtained using "DATA_Alderighi_Nicolini_Piga_figures_regressions.dta"

use "DATA_Alderighi_Nicolini_Piga_descriptive_tables.dta", clear

****TABLE 1 
bysort route:egen max_route_fare=max(fare) if period>200312 & avseats<50
bysort route:egen median_route_fare=median(fare)  if period>200312 & avseats<50
bysort route:egen mean_route_fare=mean(fare)  if period>200312 & avseats<50
bysort route:egen mode_route_fare=mode(fare)  if period>200312 & avseats<50
bysort route:egen sd_route_fare=sd(fare)  if period>200312 & avseats<50

table route if period>200312 & avseats<50,c(m  max_route_fare m median_route_fare  m mode_route_fare m mean_route_fare m  sd_route_fare)
*NOTE: the table reported in the article includes only a selection of routes from the set generated by the commands 

****TABLE 2 
bysort route:egen max_route_priceuk_less50=max(priceuk) ///
if period>200312 & avseats<50

bysort route:egen max_route_priceuk_more50=max(priceuk) ///
if period>200312 & avseats==50

bysort route:egen mean_route_priceuk_less50=mean(priceuk) ///
if period>200312 & avseats<50

bysort route:egen mean_route_priceuk_more50=mean(priceuk) ///
if period>200312 & avseats==50
 
table route if period>200312 ,c(m max_route_priceuk_less50 ///
m mean_route_priceuk_less50 m max_route_priceuk_more50 ///
m mean_route_priceuk_more50 n max_route_priceuk_less50 )

bysort route:summ(mean_route_priceuk_more50) if period>200312 & avseats==50


****TABLE 3 
gen avseat_cat=4 if avseats<=49 & avseats>=40 
replace avseat_cat=3 if avseats<=39 & avseats>=30
replace avseat_cat=2 if avseats<=29 & avseats>=20
replace avseat_cat=1 if avseats<=19 & avseats>=10
replace avseat_cat=0 if avseats<=9 & avseats>=1
replace avseat_cat=5 if avseats==50

label variable avseat_cat "Available Seats Categories"
label define seat_cat 0 "1-9" 1 "10-19" 2 "20-29" 3 "30-39" 4 "40-49" 5 ">=50" 
label values avseat_cat seat_cat

gen book_day_short=booking_day
label variable book_day_short "Booking Days"
replace book_day_short=49 if booking_day>49
label define bday_cat  49 ">=49" 
label values  book_day_short bday_cat

table book_day_short avseat_cat if period>200312, c(m priceuk n priceuk) format(%9.1f) row col

****TABLE 4 

bysort shapecode (booking_day): gen d_decrease_5=priceuk[_n]-priceuk[_n+1]<=-5  &  avseats[_n]<=avseats[_n+1] ///
if avseats[_n]<50 & avseats[_n+1]<=50 & priceuk[_n+1]<. & priceuk[_n]<.

label variable d_decrease_5 "=1 if price decrease between 2 consec bdays is >=5"
table book_day_short avseat_cat ,c(m d_decrease_5 n d_decrease_5) format(%9.2f) row col


****TABLE 5 

bysort shapecode (booking_day): gen price_change_same_LF=priceuk[_n]-priceuk[_n+1] if  avseats[_n]==avseats[_n+1] ///
& avseats[_n]<50 & avseats[_n+1]<50 & priceuk[_n+1]<. & priceuk[_n]<.

label variable price_change_same_LF "Price change over 2 consec bdays & same Load Factor"

*Columns and Rows Definition
gen cat_price_chg2=0 if price_change_same_LF<.
replace cat_price_chg2=-1 if price_change_same_LF>=-20 & price_change_same_LF<0
replace cat_price_chg2=-2 if price_change_same_LF<-20
replace cat_price_chg2=1 if price_change_same_LF>0 & price_change_same_LF<=20
replace cat_price_chg2=2 if price_change_same_LF>20 & price_change_same_LF<.

label variable cat_price_chg2 "Price change categories"
label define pchange_cat 0 "No Chg" 1 "Small Incr." 2 "Large Incr." -1 "Small Decrs" -2 "Large Decr." 
label values cat_price_chg2 pchange_cat

gen avseat_less20=avseats<=20
label variable avseat_less20 "Available Seats"
label define pseats_cat 0 ">20" 1 "<=20" 
label values avseat_less20 pseats_cat

gen book_day_less14= booking_day<=14
label variable book_day_less14 "Booking Days Categories"
label define pbday_cat 0 ">14" 1 "<=14" 
label values book_day_less14 pbday_cat

label define season 0 "Winter" 1 "Summer" 
label values summer season

label define competition 0 "High Comp." 1 "Low Comp." 
label values conc_mkt2 competition

*With the exception of the first Table command, the paper only includes the row percentages calculated from all these commands' output
table  cat_price_chg2  if abs(price_change_same_LF)<=100 ,c(m price_change_same_LF) format(%9.2f) col row
tab  avseat_less20 cat_price_chg2  if abs(price_change_same_LF)<=100  ,row
tab  book_day_less14 cat_price_chg2 if abs(price_change_same_LF)<=100  , row
tab  summer cat_price_chg2 if abs(price_change_same_LF)<=100  , row
tab conc_mkt2 cat_price_chg2 if abs(price_change_same_LF)<=100  , row


use "DATA_Alderighi_Nicolini_Piga_figures_regressions.dta", clear


****FIGURE 1 
label variable avseats "Available seats at a given booking day"
label variable inv_seat "Sold seats"

twoway (mspline priceuk inv_seat if DUB_GTW==1 & avseats<50 & avseats>0 & year>2003 & flight113==1, bands(20) n(20)) /// 
(mspline priceuk inv_seat if DUB_GTW==1 & avseats<50 &avseats>0 & year>2003 & flight115==1, bands(20) n(20)) ///
(mspline priceuk inv_seat if DUB_GTW==1 & avseats<50 & avseats>0 & year>2003& flight117==1, yaxis(2) bands(20) n(20)) ///
(mspline priceuk inv_seat if DUB_GTW==1 & avseats<50 & avseats>0 & year>2003& flight119==1, yaxis(2) bands(20) n(20)) ///
if D_seasons>3, by(,title(Pricing Profile - Route London Gatwick-Dublin)) ///
by(, legend(on position(12) span)) ///
legend(order( 1 "FR113" 2 "FR115" 3 "FR117" 4 "FR119") rowgap(tiny) colgap(tiny)) by(D_seasons)

****FIGURE 2 
gen sold=189-avseats
gen latebook=booking_day<=28   

bysort panel_id_book_day period: egen avg_sold_le50=mean(sold) if avseats<50
bysort panel_id_book_day period: egen avg_priceuk_less50=mean(priceuk) if avseats<50
 
 
gen pc_diff_sold=(sold-avg_sold_le50)/avg_sold_le50
gen pc_diff_price=(priceuk-avg_priceuk_less50)/avg_priceuk_less50

twoway (fpfit  pc_diff_price pc_diff_sold if year>2003, estcmd(glm)) ///
if period>200312, by(latebook)
 

*******ECONOMETRIC ANALYSIS
* NOTES: All xt commands are performed in the paper using bootstrap. The commands below only report the clustered s.e. 
* to calculate the bootstrapped s.e., add the following line of code:  bootstrap _b _se, reps(250) seed(10101) 
* NOTES: estimates obtained using xtivireg2 version 01.0.12 and ivreg2 version 03.0.06
 
*Calculating the INSTRUMENTS
gen slope=fare_priceuk/(189-sold)
tsset panel_id_book_day datedepart
gen slope_L7=L7.slope
gen slope_L14=L14.slope
gen slope_L21=L21.slope
egen slope_IV=rmean(slope_L7 slope_L14 slope_L21)

gen hol_book_per=((day_book>=22 & month_book==12) | (day_book<=6 & month_book==1)) 
ren hol_book_per hol_book_per1
gen hol_book_per2=((day_book>=21 & month_book==3) | (day_book<=21 & month_book==4))
gen hol_book_per3=((day_book>=25 & month_book==5) | (day_book<=6 & month_book==6))
gen hol_book_per4=((day_book>=25 & month_book==10) | (day_book<=6 & month_book==11))
gen  hol_book_per=hol_book_per1+hol_book_per2+hol_book_per3+hol_book_per4
drop hol_book_per1 hol_book_per2 hol_book_per3 hol_book_per4


****TABLE 6 FIRST COLUMN - TOBIT

tsset panel_id booking_day
xi: tobit sold  slope_IV  dow_book_1-dow_book_6   book_day1-book_day63  i.d_timedep  /// 
n_depcodes_serving_arrcode  i.week  i.route_code ///
if period>200312,  ll(139) ul(188) vce(cluster route_wk)
 
predict fitted_time3r if e(sample)

gen resid_time3r=sold-fitted_time3r


**** TABLE 6 - SECOND COLUMN - FIRST STAGE IV + TABLE 7 -FIRST COLUMN - SECOND STAGE IV
xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312  & avseats<50, ///
fe robust first cluster(panel_id route_wk)

 
****TABLE 7 - SECOND COLUMN FE-OLS
*to exclude the singleton groups from FE-OLS 
gen  sample_iv =e(sample)
bysort panel_id: egen counter=count(lnpriceuk2) if sample_iv==1

xtreg lnpriceuk2 sold book_day1-book_day63 month_booa2-month_booa12 resid_time3r  ///
if period>200312   & avseats<50 & sample_iv==1 & counter>1 ,  fe   

****TABLE 8 

gen sold_norm=sold-163
gen booking_period7=0
replace booking_period7=1 if booking_day<=7


gen sold_norm_booking7=sold_norm* booking_period7
gen slope_IV_booking7=slope_IV*booking_period7

xi: xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold_norm sold_norm_booking7= hol_book_per slope_IV slope_IV_booking7) ///
if period>200312  & avseats<50, fe robust  cluster(panel_id route_wk)
 
gen booking_period10=0
replace booking_period10=1 if booking_day<=10

gen sold_norm_booking10=sold_norm* booking_period10
gen slope_IV_booking10=slope_IV*booking_period10

xi: xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r  ///
(sold_norm sold_norm_booking10= hol_book_per slope_IV slope_IV_booking10) if period>200312 ///
& avseats<50, fe robust cluster(panel_id route_wk)

gen booking_period14=0
replace booking_period14=1 if booking_day<=14

gen sold_norm_booking14=sold_norm* booking_period14
gen slope_IV_booking14=slope_IV*booking_period14

xi: xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold_norm sold_norm_booking14= hol_book_per slope_IV slope_IV_booking14) ///
if period>200312  & avseats<50, fe robust cluster(panel_id route_wk)

 
****TABLE 9 - VOLATILITY
 
egen gr_flight_month=group(flightcode month)

bysort gr_flight_month: egen sd_temp=sd(sold) if booking_day==1 & avseats<50
bysort gr_flight_month: egen sd_gr_flight_month=mean(sd_temp) if avseats<50
egen sd_sample=sd(sold)

gen high_vol=sd_gr_flight_month>sd_sample & avseats<50
gen low_vol=sd_gr_flight_month<sd_sample & avseats<50
   
xi: xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312 & high_vol==1 & avseats<50 ///
, fe robust first cluster(panel_id route_wk)

xi: xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312 & low_vol==1 & avseats<50 ///
, fe robust first cluster(panel_id route_wk)

 
****TABLE 10

*morning: 6.00-11.00
xi:  xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312 & morning==1 & avseats<50, ///
fe robust cluster(panel_id route_wk)
 
*evening: 16.00-22.15
 xi:   xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312 & evening==1  & avseats<50, ///
fe robust cluster(panel_id route_wk)

* Winter 
xi:  xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312 & (D_seasons==4 | D_seasons==6 ) & avseats<50, ///
fe robust cluster(panel_id route_wk)
 
* Summer 
xi:  xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312 & (D_seasons==5 | D_seasons==7) & avseats<50, ///
fe robust cluster(panel_id route_wk)


****TABLE 11 

tsset panel_id booking_day
*short haul
xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312 & short_haul==1 & avseats<50, ///
fe robust cluster(panel_id route_wk)
 
*medium haul
xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312 & medium_haul==1 & avseats<50, ///
fe robust cluster(panel_id route_wk)
 
*Low Competition
xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312 &  conc_mkt2==1 & avseats<50, ///
fe robust cluster(panel_id route_wk)

*High Competition
xtivreg2 lnpriceuk2 book_day1-book_day63  month_booa2-month_booa12 resid_time3r ///
(sold= hol_book_per slope_IV) if period>200312 &  conc_mkt2==0 & avseats<50, ///
fe robust cluster(panel_id route_wk)
 


